<?php
namespace control;

/*
*销售利润表类
*/

class saleProfitClass extends adIndexClass
{
	private const page  = 'saleProfit';
	private const title = '销售利润表';

    /**
     * 字段初始化
     */
    public function fieldInit($fun): array
    {
        switch ($fun) {
            default:
                $tr = [
                    'recordDate'          => [
                        'name'  => '单据日期',
                        'width' => 180,
                        'sort' => 1,
                    ],
                    'orderNo' => [
                        'name'  => "单据编号",
                        'width' => 150,
                        'sort' => 1,
                    ],
                    'contract'  => [
                        'name'  => "合同编号",
                        'width' => 100,
                    ],
                    'type'         => [
                        'name'  => '业务类型',
                        'width' => 100,
                    ],
                    'companyName'          => [
                        'name'  => '客户',
                        'width' => 100,
                    ],
                    'manager'          => [
                        'name'  => '销售员',
                        'width' => 170,
                    ],
                    'code'          => [
                        'name'  => '产品编码',
                        'width' => 180,
                        'sort' => 1,
                    ],
                    'materialName'          => [
                        'name'  => '产品名称',
                        'width' => 100,
                    ],
                    'model'          => [
                        'name'  => '规格型号',
                        'width' => 150,
                        'sort' => 1,
                    ],
                    'unit'          => [
                        'name'  => '单位',
                        'width' => 150,
                    ],
                    'out_cost_num'          => [
                        'name'  => '发货数量',
                        'width' => 150,
                    ],
                    'out_Money'          => [
                        'name'  => '发货金额',
                        'width' => 150,
                    ],
                    'in_cost_num'          => [
                        'name'  => '退货数量',
                        'width' => 150,
                    ],
                    'refund_Money'          => [
                        'name'  => '退货金额',
                        'width' => 150,
                    ],
                    'actual_send'          => [
                        'name'  => '实际发货数量',
                        'width' => 150,
                    ],
                    'income'          => [
                        'name'  => '销售收入(' . static::$unit . ')',
                        'width' => 150,
                    ],
                    'freightMoney'          => [
                        'name'  => '分摊运费(' . static::$unit . ')',
                        'width' => 150,
                    ],
                    'out_cost_money'          => [
                        'name'  => '销售出库金额(' . static::$unit . ')',
                        'width' => 150,
                    ],
                    'in_cost_money'          => [
                        'name'  => '退货入库金额(' . static::$unit . ')',
                        'width' => 150,
                    ],
                    'cost'          => [
                        'name'  => '销售成本(' . static::$unit . ')',
                        'width' => 150,
                    ],
                    'grossProfit'          => [
                        'name'  => '毛利(' . static::$unit . ')',
                        'width' => 150,
                    ],
                    'grossMargin'          => [
                        'name'  => '毛利率',
                        'width' => 150,
                    ]
                ];
        }
        return $tr;
    }

    /*列表页-查询条件*/
    public function homeOptions($isAsync = true)
    {
        //生成查询表单
        $html = $this->tableShow([
            [
                [
                    "单据编号" => text(array("name" => "orderNo")),
                    "合同编号"   => text(array("name" => "contractNo")),
                    "产品编码"   => text(array("name" => "code")),
                ],
                [
                    "产品名称"   => text(array("name" => "materialName")),
                    "客户名称"   => clientProfileClass::kehuSelect(array('title' => '')),
                    '销售员'  => selectMultiple(["name" => "stid", 'table' => "staff", 'sqlId' => 'stid', 'sqlField' => 'name', 'class' => 'width200']),
                ],
                [
                    "单据日期" => text(array("name" => "createTime", 'laydate' => ['type' => 'date', 'range' => '~'])),
                ]
            ]
        ],1);
        //返回
        if (!$isAsync) {
            return $html;
        } else {
            return json_encode(['html' => $html], 320);
        }
    }

	/*列表-查询条件*/
	public function home()
	{
        $html = $this->homeOptions(false);
        //控件
        $button = "";
        if ($this->power(self::page, "export")) {
            $button .="<span class='spanButton' onclick=\"layer('importTemplate','exportFrame',{cla:'".self::page."'})\">导出</span>";
        }
		//返回
		return $this->homeFun($this->search($html, layout: 'pop', toolButton: $button));
	}

	/*客户列表-查询结果*/
	public function homeSearch()
	{
		//like查询数组
		$like = array(
			"b.orderNo" => $this->post('orderNo'),//证件编号
			"c.name"    => $this->post('materialName'),
			"c.code"    => $this->post('code'),
		);
		//全等查询数组
		$equal = array(
			"b.manager" => $this->post('stid'),
			"b.khid"    => $this->post('khid'),
			"a.coid"    => self::$coid,
			"b.state"   => '已确认',
		);
		$and   = "";
		if ($this->post('contractNo')) {
			$and .= " and b.contractId in (select id from sales_contract where orderNo like '%" . $this->post('contractNo') . "%' and coid = '" . self::$coid . "')";
		}
		if ($this->post('createTime')) {
			//单据日期
			$timeArr = explode(' ~ ', $this->post('createTime'));
			$and     .= " and b.recordDate >='{$timeArr[0]}' and b.recordDate <='{$timeArr[1]}'";
		}
		//获取查询结果
		$where = search(array("like" => $like, "=" => $equal)) . $and;
        //排序
        $orderBy = $this->listSort(array(
            "search"  => $this->post('list_sort_key') ?: 'a.list desc,a.time',
            "sort" => $this->post('list_sort') ?: "desc",
            "array"   => array(),
        ));
		$page                        = $this->page(array(
			"table" => "sale_order_detail as a join sale_order as b on a.orderId = b.id join material as c on c.id = a.materialId",
			'field' => "a.*,b.orderNo,b.recordDate,b.contractId,b.khid,c.code,c.name as materialName,c.model,b.deliveryDate,b.manager,b.type",
            "where" => $where,
            "order" => $orderBy['orderBy'],
		));
		$_SESSION[self::page]['sql'] = $page['fullSql'];
        //获取自定义字段参数
        $installSearch = $this->installSearch();
        $body          = [];
        //表格控件区
        $liButton = array(
            $this->checkAll("listForm", "listId")
        );
		//获取记录
		foreach ($page['array'] as $array) {
            //列表控件
            $dominate = array(
                "box" => array("name" => "listId", "value" => $array['id']),
            );
            $liButton[] = $this->dominate($dominate);
            //发货出库金额
            $out_cost = find(array(
                "table"=>" inventory_changes_detail as detail join inventory_changes as main on detail.orderId = main.id join inventory_detail as mx on mx.id = detail.sourceId ",
                "field"=>" ifnull(sum(detail.num),0) as num,ifnull(sum(detail.money),0) as money ",
                "single"=>" main.state in ('已出库','已复核') and main.coid = '".self::$coid."' and mx.sourceTable = 'sale_send_order_detail' and mx.sourceId in (select id from sale_send_order_detail where saleOrderMxId = '$array[id]') "
            ));
            //退货入库金额
            $in_cost = find(array(
                "table"=>" inventory_changes_detail as detail join inventory_changes as main on detail.orderId = main.id join inventory_detail as mx on mx.id = detail.sourceId ",
                "field"=>" ifnull(sum(detail.num),0) as num,ifnull(sum(detail.money),0) as money ",
                "single"=>" main.state in ('已入库','已复核') and main.coid = '".self::$coid."' and mx.sourceTable = 'sale_refund_order_detail' and mx.sourceId in (select id from sale_refund_order_detail where sourceId = '$array[id]') "
            ));
            $cost   = self::formattingFigure($out_cost['money'] - $in_cost['money']);
            //销售发货金额
            $out_Money = find(array(
                "table"=>" inventory_detail as detail join inventory as main on detail.orderId = main.id ",
                "field"=>" ifnull(sum(detail.money),0) as money ",
                "single"=>" detail.coid = '" . self::$coid . "' and detail.sourceTable = 'sale_send_order_detail' and detail.sourceId in (select id from sale_send_order_detail where saleOrderMxId = '$array[id]') "
            ))['money'];
            //退货金额
            $refund_Money = find(array(
                "table"=>" inventory_detail as detail join inventory as main on detail.orderId = main.id ",
                "field"=>" ifnull(sum(detail.money),0) as money ",
                "single"=>" detail.coid = '" . self::$coid . "' and detail.sourceTable = 'sale_refund_order_detail' and detail.sourceId in (select id from sale_refund_order_detail where sourceId = '$array[id]') "
            ))['money'];
            $income = round($out_Money - $refund_Money, 2);  //收入
            //分摊运费
            $freightMoney = find(array(
                "table"=>" sale_send_order_loading as loading join sale_send_order as ord on loading.orderId = ord.id ",
                "field"=>" ifnull(sum(loading.freight),0) as freight ",
                "single"=>" ord.coid = '".self::$coid."' and ord.state = '已确认' and loading.saleOrderMxId = '$array[id]' "
            ))['freight'];
            //本行初始化
            $row = [];
            foreach ($installSearch['key'] as $key) {
                switch ($key) {
                    case "contract":
                        $contract    = query('sales_contract', "id = '$array[contractId]'");
                        $row[] = $contract['orderNo'];
                        break;
                    case "companyName":
                        $kehu        = query('kehu', "khid = '$array[khid]'");
                        $row[] = $kehu['companyName'];
                        break;
                    case "manager":
                        $row[] = self::findStaff($array['manager']);
                        break;
                    case "out_cost_num":
                        $row[] = self::lostZero($out_cost['num'],'numPrecision');
                        break;
                    case "out_Money":
                        $row[] = self::lostZero($out_Money,'amount');
                        break;
                    case "in_cost_num":
                        $row[] = self::lostZero($in_cost['num'],'numPrecision');
                        break;
                    case "refund_Money":
                        $row[] = self::lostZero($refund_Money,'amount');
                        break;
                    case "actual_send":
                        $row[] = self::formattingFigure($out_cost['num'] - $in_cost['num'], 'numPrecision');
                        break;
                    case "income":
                        $row[] = $income;
                        break;
                    case "freightMoney":
                        $row[] = self::lostZero($freightMoney,'amount');
                        break;
                    case "out_cost_money":
                        $row[] = self::lostZero($out_cost['money']);
                        break;
                    case "in_cost_money":
                        $row[] = self::lostZero($in_cost['money']);
                        break;
                    case "cost":
                        $row[] = $cost;
                        break;
                    case "grossProfit":
                        $row[] = self::formattingFigure($income - $cost-$freightMoney,'amount');
                        break;
                    case "grossMargin":
                        $row[] = machiningOrderClass::formatRatio($income - $cost-$freightMoney,$income);
                        break;
                    default:
                        $row[] = isset($array[$key]) && $array[$key] ? $array[$key] : '';
                }
            }
            $body[] = $row;
		}
		//控件
		$button = "";
		//统计
        $totalSql = find(array(
            "table"=>" sale_order_detail as a join sale_order as b on a.orderId = b.id join material as c on c.id = a.materialId ",
            "field"=>" a.id ",
            "where"=>$where
        ));
		$ids = implode("','",(array)array_column($totalSql['array'],'id'));
        //发货出库金额
        $out_cost = find(array(
            "table"=>" inventory_changes_detail as detail join inventory_changes as main on detail.orderId = main.id join inventory_detail as mx on mx.id = detail.sourceId ",
            "field"=>" ifnull(sum(detail.num),0) as num,ifnull(sum(detail.money),0) as money ",
            "single"=>" main.state in ('已出库','已复核') and main.coid = '".self::$coid."' and mx.sourceTable = 'sale_send_order_detail' and mx.sourceId in (select id from sale_send_order_detail where saleOrderMxId in ('$ids')) "
        ));
        //退货入库金额
        $in_cost = find(array(
            "table"=>" inventory_changes_detail as detail join inventory_changes as main on detail.orderId = main.id join inventory_detail as mx on mx.id = detail.sourceId ",
            "field"=>" ifnull(sum(detail.num),0) as num,ifnull(sum(detail.money),0) as money ",
            "single"=>" main.state in ('已入库','已复核') and main.coid = '".self::$coid."' and mx.sourceTable = 'sale_refund_order_detail' and mx.sourceId in (select id from sale_refund_order_detail where sourceId  in ('$ids')) "
        ));
        $cost   = self::formattingFigure($out_cost['money'] - $in_cost['money']);
        //销售发货金额
        $out_Money = find(array(
            "table"=>" inventory_detail as detail join inventory as main on detail.orderId = main.id ",
            "field"=>" ifnull(sum(detail.money),0) as money ",
            "single"=>" detail.coid = '" . self::$coid . "' and detail.sourceTable = 'sale_send_order_detail' and detail.sourceId in (select id from sale_send_order_detail where saleOrderMxId  in ('$ids')) "
        ))['money'];
        //退货金额
        $refund_Money = find(array(
            "table"=>" inventory_detail as detail join inventory as main on detail.orderId = main.id ",
            "field"=>" ifnull(sum(detail.money),0) as money ",
            "single"=>" detail.coid = '" . self::$coid . "' and detail.sourceTable = 'sale_refund_order_detail' and detail.sourceId in (select id from sale_refund_order_detail where sourceId  in ('$ids')) "
        ))['money'];
        $income = round($out_Money - $refund_Money, 2);  //收入
        //分摊运费
        $freightMoney = find(array(
            "table"=>" sale_send_order_loading as loading join sale_send_order as ord on loading.orderId = ord.id ",
            "field"=>" ifnull(sum(loading.freight),0) as freight ",
            "single"=>" ord.coid = '".self::$coid."' and ord.state = '已确认' and loading.saleOrderMxId in ('$ids') "
        ))['freight'];
        //毛利率
        $grossProfitRate = ($income > 0 ? round((($income - $cost - $freightMoney) / $income) * 100, 1) : 0);
        /*$button.=saleIncomeClass::titleSpan('发货金额合计',self::formattingFigure($out_Money));
        $button.=saleIncomeClass::titleSpan('退货金额合计',self::formattingFigure($refund_Money));
        $button.=saleIncomeClass::titleSpan('运费合计',self::formattingFigure($freightMoney));
        $button.=saleIncomeClass::titleSpan('发货出库金额合计',self::formattingFigure($out_cost['money']));
        $button.=saleIncomeClass::titleSpan('退货入库金额合计',self::formattingFigure($in_cost['money']));
        $button.=saleIncomeClass::titleSpan('销售成本合计',self::formattingFigure($cost));
        $button.=saleIncomeClass::titleSpan('毛利合计',self::formattingFigure($income - $cost-$freightMoney,'amount'));
        $button.=saleIncomeClass::titleSpan('毛利率',$grossProfitRate.'%');*/
        $total = [
            'out_Money' => self::formattingFigure($out_Money),
            'refund_Money' => self::formattingFigure($refund_Money),
            'freightMoney' => self::formattingFigure($freightMoney),
            'out_cost_money' => self::formattingFigure($out_cost['money']),
            'in_cost_money' => self::formattingFigure($in_cost['money']),
            'cost' => self::formattingFigure($cost),
            'grossProfit' => self::formattingFigure($income - $cost-$freightMoney,'amount'),
            'grossMargin' => $grossProfitRate.'%',
        ];
        //生成页面
        $li = $this->li(array(
            "body" => $body,
            "install" => $installSearch,
            "button" => array("html" => $liButton),
            "orderBySign" => $orderBy,
            "page" => $page['para'],
            "total" => $total,
        ));
		//返回
		$json['html'] = $page['hint'] . $button . "<form name='listForm'>" . $li . "</form>" . $page['button'] . clear;
		return json_encode($json, 320);
	}

	/**
	 * 销售成本
	 */
	public static function cost($saleOrderMxId, $saleNum, $round = true): float
	{
		$saleOrderMx = query('sale_order_detail',"id = '$saleOrderMxId'");
		$saleOrder = query('sale_order',"id = '$saleOrderMx[orderId]'");
		$price = inventoryDefaultClass::getInventoryPrice($saleOrderMx['materialId'],time:$saleOrder['recordDate']);
		return $saleOrderMx['id'] ? (($round === true) ? self::formattingFigure( $price* $saleNum, 'amount') : $price * $saleNum) : 0;
	}

	/**
	 * 导出
	 */
	public function exportData()
	{
        $templateId = $this->get('templateId');
        $checkedId = $this->get('checkedId')?explode(',',$this->get('checkedId')):[];
		if (!$this->power(self::page, 'export')) {
			$_SESSION['warn'] = '你还没有导出的权限';
        }  else {
            $data = [];
            /***********标题**********/
            $title =[];
            $tempFieldAll = importTemplateClass::templateField(self::page, 'exportFieldInit', id:$templateId);
            foreach ($tempFieldAll as $array) {
                if ($array['display']){
                    $title[] = $array['aliasName'];
                }
            }
            //判断是否选中导出
            if (!self::is_null_array($checkedId)){
                $result = find(array(
                    "table" => "sale_order_detail as a join sale_order as b on a.orderId = b.id join material as c on c.id = a.materialId",
                    'field' => "a.*,b.orderNo,b.recordDate,b.contractId,b.khid,c.code,c.name as materialName,c.model,b.deliveryDate,b.manager,b.type",
                    "where" => "a.id in ('".implode("','", $checkedId)."') order by a.list desc,a.time desc",
                ))['array'];
            }else{
                $sql   = $_SESSION[self::page]['sql'];
                $result   = sqlFind($sql);
            }
            //导出数据生成
            foreach ($result as $array){
                $column = [];
                $contract    = query('sales_contract', "id = '$array[contractId]'");
                $kehu        = query('kehu', "khid = '$array[khid]'");
                //发货出库金额
                $out_cost = find(array(
                    "table"=>" inventory_changes_detail as detail join inventory_changes as main on detail.orderId = main.id join inventory_detail as mx on mx.id = detail.sourceId ",
                    "field"=>" ifnull(sum(detail.num),0) as num,ifnull(sum(detail.money),0) as money ",
                    "single"=>" main.state in ('已出库','已复核') and main.coid = '".self::$coid."' and mx.sourceTable = 'sale_send_order_detail' and mx.sourceId in (select id from sale_send_order_detail where saleOrderMxId = '$array[id]') "
                ));
                //退货入库金额
                $in_cost = find(array(
                    "table"=>" inventory_changes_detail as detail join inventory_changes as main on detail.orderId = main.id join inventory_detail as mx on mx.id = detail.sourceId ",
                    "field"=>" ifnull(sum(detail.num),0) as num,ifnull(sum(detail.money),0) as money ",
                    "single"=>" main.state in ('已入库','已复核') and main.coid = '".self::$coid."' and mx.sourceTable = 'sale_refund_order_detail' and mx.sourceId in (select id from sale_refund_order_detail where sourceId = '$array[id]') "
                ));
                $cost   = self::formattingFigure($out_cost['money'] - $in_cost['money']);
                //销售发货金额
                $out_Money = find(array(
                    "table"=>" inventory_detail as detail join inventory as main on detail.orderId = main.id ",
                    "field"=>" ifnull(sum(detail.money),0) as money ",
                    "single"=>" detail.coid = '" . self::$coid . "' and detail.sourceTable = 'sale_send_order_detail' and detail.sourceId in (select id from sale_send_order_detail where saleOrderMxId = '$array[id]') "
                ))['money'];
                //退货金额
                $refund_Money = find(array(
                    "table"=>" inventory_detail as detail join inventory as main on detail.orderId = main.id ",
                    "field"=>" ifnull(sum(detail.money),0) as money ",
                    "single"=>" detail.coid = '" . self::$coid . "' and detail.sourceTable = 'sale_refund_order_detail' and detail.sourceId in (select id from sale_refund_order_detail where sourceId = '$array[id]') "
                ))['money'];
                $income = round($out_Money - $refund_Money, 2);  //收入
                //分摊运费
                $freightMoney = find(array(
                    "table"=>" sale_send_order_loading as loading join sale_send_order as ord on loading.orderId = ord.id ",
                    "field"=>" ifnull(sum(loading.freight),0) as freight ",
                    "single"=>" ord.coid = '".self::$coid."' and ord.state = '已确认' and loading.saleOrderMxId = '$array[id]' "
                ))['freight'];
                foreach ($tempFieldAll as $templateField){
                    if ($templateField['display']){
                        $column[] = match ($templateField['columnName']){
                            '单据日期'=>$array['recordDate'],
                            '单据编号'=>$array['orderNo'],
                            '合同编号'=>    $contract['orderNo'],
                            '业务类型'=>$array['type'],
                            '客户'=>$kehu['companyName'],
                            '销售员'=>self::findStaff($array['manager']),
                            '产品编码'=>$array['code'],
                            '产品名称'=>$array['materialName'],
                            '规格型号'=>$array['model'],
                            '单位'=>$array['unit'],
                            '发货数量'=>self::lostZero($out_cost['num'],'numPrecision'),
                            '发货金额'=> self::lostZero($out_Money,'amount'),
                            '退货数量'=> self::lostZero($in_cost['num'],'numPrecision'),
                            '退货金额'=> self::lostZero($refund_Money,'amount'),
                            '实际发货数量'=>self::formattingFigure($out_cost['num'] - $in_cost['num'], 'numPrecision'),
                            '销售收入'=>$income,
                            '分摊运费'=> self::lostZero($freightMoney,'amount'),
                            '销售出库金额'=>self::lostZero($out_cost['money']),
                            '退货入库金额'=> self::lostZero($in_cost['money']),
                            '销售成本'=>  $cost,
                            '毛利'=>self::formattingFigure($income - $cost-$freightMoney,'amount'),
                            '毛利率'=>machiningOrderClass::formatRatio($income - $cost-$freightMoney,$income),
                            default=>'',
                        };
                    }
                }
                $data[] = $column;
            }
			$text = $this->control['name'] . "导出了" . self::title;
			$this->logText(["type" => self::page, "text" => $text]);
			$this->excelOut($title, $data, "销售利润表.xlsx");
            exit(0);
		}
		header("Location:" . getenv("HTTP_REFERER"));
	}
    /**
     * 导出模板字段初始化
     * @return array[]
     */
    public function exportFieldInit($classify=''): array
    {
        return match ($classify) {
            default=>  [
                [
                    'columnName'=>'单据日期',
                    'aliasName'=>'单据日期',
                ],
                [
                    'columnName'=>'单据编号',
                    'aliasName'=>'单据编号',
                ],
                [
                    'columnName'=>'合同编号',
                    'aliasName'=>'合同编号',
                ],
                [
                    'columnName'=>'业务类型',
                    'aliasName'=>'业务类型',
                ],
                [
                    'columnName'=>'客户',
                    'aliasName'=>'客户',
                ],
                [
                    'columnName'=>'销售员',
                    'aliasName'=>'销售员',
                ],
                [
                    'columnName'=>'产品编码',
                    'aliasName'=>'产品编码',
                ],
                [
                    'columnName'=>'产品名称',
                    'aliasName'=>'产品名称',
                ],
                [
                    'columnName'=>'规格型号',
                    'aliasName'=>'规格型号',
                ],
                [
                    'columnName'=>'单位',
                    'aliasName'=>'单位',
                ],
                [
                    'columnName'=>'发货数量',
                    'aliasName'=>'发货数量',
                ],
                [
                    'columnName'=>'发货金额',
                    'aliasName'=>'发货金额',
                ],
                [
                    'columnName'=>'退货数量',
                    'aliasName'=>'退货数量',
                ],
                [
                    'columnName'=>'退货金额',
                    'aliasName'=>'退货金额',
                ],
                [
                    'columnName'=>'实际发货数量',
                    'aliasName'=>'实际发货数量',
                ],
                [
                    'columnName'=>'销售收入',
                    'aliasName'=>'销售收入('.static::$unit.')',
                ],
                [
                    'columnName'=>'分摊运费',
                    'aliasName'=>'分摊运费('.static::$unit.')',
                ],
                [
                    'columnName'=>'销售出库金额',
                    'aliasName'=>'销售出库金额('.static::$unit.')',
                ],
                [
                    'columnName'=>'退货入库金额',
                    'aliasName'=>'退货入库金额('.static::$unit.')',
                ],
                [
                    'columnName'=>'销售成本',
                    'aliasName'=>'销售成本('.static::$unit.')',
                ],
                [
                    'columnName'=>'毛利',
                    'aliasName'=>'毛利('.static::$unit.')',
                ],
                [
                    'columnName'=>'毛利率',
                    'aliasName'=>'毛利率',
                ],
            ]
        };
    }
}

